package scsc.pm;

import java.util.*;

import scsc.pub.data.NohouseBuilding;
import scsc.pub.db.*;
import scsc.pub.tools.*;
import java.util.ArrayList;
import scsc.web.xzgl.building.report.data.RoomReportData;
import scsc.web.xzgl.building.report.data.RoomDetailRptData;
import scsc.pub.exception.*;

public class RoomReportPm {
  private static Logger logger = Logger.getLogger(RoomReportPm.class);
  public RoomReportPm() {
  }

  public void selectRoomReport(DBManage dbmanage, RoomReportData formdata,
                                  PageListData footer) {
	String sqlStr="";
	if (formdata.getIsvacant()!=null && formdata.getIsvacant().equals("1")){
		sqlStr="select new scsc.web.xzgl.building.report.data.RoomReportData(''";
		sqlStr=sqlStr+",h.buildId,h.buildName";
		if (formdata.getUnit()!=null && formdata.getUnit().intValue()!=0){
			sqlStr=sqlStr+",r.unit";
		}else
			sqlStr=sqlStr + ",0";
		if (formdata.getFloor()!=null && formdata.getFloor().intValue()!=0){
			sqlStr=sqlStr+",r.floor";
		}else
			sqlStr=sqlStr + ",0";
		if (formdata.getRoomType()!=null && formdata.getRoomType().trim().length()>0 && !formdata.getRoomType().trim().equals("204000")){
			sqlStr=sqlStr+",r.roomType,d.dictItemName";
		}else
			sqlStr=sqlStr + ",'',''";
		sqlStr+=",'',''";
		sqlStr+=",'1'";
		sqlStr+=",count(*) as count";
		sqlStr+=")";
		sqlStr+=" from scsc.pub.data.RoomMng as r";
		sqlStr=sqlStr+",scsc.pub.data.HouseBuilding as h";
		if (formdata.getRoomType()!=null && formdata.getRoomType().trim().length()>0 && !formdata.getRoomType().trim().equals("204000")){
			sqlStr=sqlStr+",scsc.pub.data.SysDictItem as d";
		}
		sqlStr+=" where r.isvacantRoom='1' and r.builderId=h.buildId";
		if (formdata.getBuildId()!=null && formdata.getBuildId().intValue()!=0){
			sqlStr=sqlStr+" and r.builderId=" + formdata.getBuildId();
		}
		if (formdata.getRoomType()!=null && formdata.getRoomType().trim().length()>0 && !formdata.getRoomType().trim().equals("204000")){
			sqlStr=sqlStr+" and r.roomType=d.dictItemId and d.dictId='000204' and r.roomType='"+formdata.getRoomType().trim()+"'";
		}
		if (formdata.getUnit()!=null && formdata.getUnit().intValue()!=0){
			sqlStr+=" and r.unit=" + formdata.getUnit();
		}
		if (formdata.getFloor()!=null && formdata.getFloor().intValue()!=0){
			sqlStr+=" and r.floor="+ formdata.getFloor();
		} 
		sqlStr+=" group by h.buildId,h.buildName";

		if (formdata.getUnit()!=null && formdata.getUnit().intValue()!=0){
			sqlStr+=",r.unit";
		}
		if (formdata.getFloor()!=null && formdata.getFloor().intValue()!=0){
			sqlStr+=",r.floor";
		}
		if (formdata.getRoomType()!=null && formdata.getRoomType().trim().length()>0 && !formdata.getRoomType().trim().equals("204000")){
			sqlStr+=",r.roomType,d.dictItemName";
		}
	}else{
			sqlStr="select new scsc.web.xzgl.building.report.data.RoomReportData(";
			if ( formdata.getIsvacant()==null || formdata.getIsvacant()!=null && formdata.getIsvacant().trim().length()==0)
				sqlStr=sqlStr + "dept.deptname";
			else
				sqlStr=sqlStr + "''";
			if (formdata.getBuildId()!=null && formdata.getBuildId().intValue()!=0){
				sqlStr=sqlStr+",hb.buildId,hb.buildName";
			}else
				sqlStr=sqlStr+ ",0,''";
			if (formdata.getUnit()!=null && formdata.getUnit().intValue()!=0){
				sqlStr=sqlStr+",r.unit";
			}else
				sqlStr=sqlStr + ",0";
				
			if (formdata.getFloor()!=null && formdata.getFloor().intValue()!=0){
				sqlStr=sqlStr+",r.floor";
			}else
				sqlStr=sqlStr + ",0";
			if (formdata.getRoomType()!=null && formdata.getRoomType().trim().length()>0 && !formdata.getRoomType().trim().equals("204000")){
				sqlStr=sqlStr+",r.roomType,d1.dictItemName";
			}else
				sqlStr=sqlStr + ",'',''";
			if (formdata.getPropertyRight()!=null && formdata.getPropertyRight().trim().length()>0 && !formdata.getPropertyRight().trim().equals("211000")){
				sqlStr=sqlStr+",r.propertyRight,d2.dictItemName";
			}else
				sqlStr=sqlStr + ",'',''";
			if (formdata.getIsvacant()!=null && formdata.getIsvacant().length()>0){
				sqlStr=sqlStr+",r.isvacantRoom";
			}else
				sqlStr=sqlStr + ",''";
			sqlStr=sqlStr + ",count(*) as count";
			sqlStr=sqlStr + ")";
			sqlStr=sqlStr + " from scsc.pub.data.HouseAlteration as h,scsc.pub.data.RoomMng as r,scsc.pub.data.HouseBuilding as hb";
			if ( formdata.getIsvacant()==null || formdata.getIsvacant()!=null && formdata.getIsvacant().trim().length()==0)
				sqlStr=sqlStr + ",scsc.pub.data.Department as dept";//added by guoy at 20060906
			if (formdata.getRoomType()!=null && formdata.getRoomType().trim().length()>0 && !formdata.getRoomType().trim().equals("204000")){
				sqlStr=sqlStr+",scsc.pub.data.SysDictItem as d1";
			}
			if (formdata.getPropertyRight()!=null && formdata.getPropertyRight().trim().length()>0 && !formdata.getPropertyRight().trim().equals("211000")){
				sqlStr=sqlStr+",scsc.pub.data.SysDictItem as d2";
			}
			sqlStr=sqlStr + " where h.roomId=r.roomId and r.builderId=hb.buildId and h.isnowHolder='1'";
			if ( formdata.getIsvacant()==null || formdata.getIsvacant()!=null && formdata.getIsvacant().trim().length()==0)
				sqlStr=sqlStr + " and h.holderCorpName=dept.deptcode"; //added by guoy at 20060906
			if (formdata.getHolderCorpName()!=null && formdata.getHolderCorpName().trim().length()>0){
				sqlStr=sqlStr+ " and h.holderCorpName like '%" + formdata.getHolderCorpName().trim() + "%'";
			}
			if (formdata.getBuildId()!=null && formdata.getBuildId().intValue()!=0){
				sqlStr=sqlStr+" and hb.buildId=" + formdata.getBuildId();
			}
		
			if (formdata.getUnit()!=null && formdata.getUnit().intValue()!=0){
				sqlStr=sqlStr+" and r.unit=" + formdata.getUnit();
			}
			if (formdata.getFloor()!=null && formdata.getFloor().intValue()!=0){
				sqlStr=sqlStr+" and r.floor=" + formdata.getFloor();
			}
			if (formdata.getRoomType()!=null && formdata.getRoomType().trim().length()>0 && !formdata.getRoomType().trim().equals("204000")){
				sqlStr=sqlStr+" and r.roomType like '%" +formdata.getRoomType().trim()+ "%' and r.roomType=d1.dictItemId and d1.dictId='000204'";
			}
			if (formdata.getPropertyRight()!=null && formdata.getPropertyRight().trim().length()>0 && !formdata.getPropertyRight().trim().equals("211000")){
				sqlStr=sqlStr+" and r.propertyRight like '%" + formdata.getPropertyRight().trim()+ "%' and r.propertyRight=d2.dictItemId and d2.dictId='000211'";
			}
			if (formdata.getIsvacant()!=null && formdata.getIsvacant().trim().length()>0){
				sqlStr=sqlStr+" and r.isvacantRoom like '%"+ formdata.getIsvacant().trim()+ "%'";
			}
			String flag="0";
			if ( formdata.getIsvacant()==null || formdata.getIsvacant()!=null && formdata.getIsvacant().trim().length()==0){
				sqlStr=sqlStr + " group by dept.deptname";
				flag="1";
			}
			
				if (formdata.getBuildId()!=null && formdata.getBuildId().intValue()!=0){
					if (flag.equals("1"))
						sqlStr=sqlStr+",hb.buildId,hb.buildName";
					else{
						sqlStr+=" group by hb.buildId,hb.buildName";
						flag="1";
					}
				}
		
				if (formdata.getUnit()!=null && formdata.getUnit().intValue()!=0){
					if (flag.equals("1"))
						sqlStr=sqlStr+",r.unit";
					else{
						sqlStr+=" group by r.unit";
						flag="1";
					}
				}
				if (formdata.getFloor()!=null && formdata.getFloor().intValue()!=0){
					if (flag.equals("1"))
						sqlStr=sqlStr+",r.floor";
					else{
						sqlStr+=" group by r.floor";
						flag="1";
					}
				}
				if (formdata.getRoomType()!=null && formdata.getRoomType().length()>0 && !formdata.getRoomType().trim().equals("204000")){
					if (flag.equals("1"))
						sqlStr=sqlStr+",r.roomType,d1.dictItemName";
					else{
						sqlStr+=" group by r.roomType,d1.dictItemName";
						flag="1";
					}
				}
				if (formdata.getPropertyRight()!=null && formdata.getPropertyRight().length()>0 && !formdata.getPropertyRight().trim().equals("211000")){
					if (flag.equals("1"))
						sqlStr=sqlStr+",r.propertyRight,d2.dictItemName";
					else{
						sqlStr+=" group by r.propertyRight,d2.dictItemName";
						flag="1";
					}
				}
				if (formdata.getIsvacant()!=null && formdata.getIsvacant().length()>0){
					if (flag.equals("1"))
						sqlStr=sqlStr+",r.isvacantRoom";
					else{
						sqlStr+=" group by r.isvacantRoom";
						flag="1";
					}
				}
				if ( formdata.getIsvacant()==null || formdata.getIsvacant()!=null && formdata.getIsvacant().trim().length()==0){
					sqlStr=sqlStr + " order by dept.deptname";
				}
		}
	    dbmanage.sqlQueryPage(sqlStr, footer);

  }

  public void selectRoomDetailRpt(DBManage dbmanage,RoomReportData formdata,PageListData footer){
	  String sqlStr="";
	  if (formdata.getIsvacant()!=null && formdata.getIsvacant().equals("1")){
		    sqlStr = "select new scsc.web.xzgl.building.room.data.RoomData" +
		    "(r.roomId,r.builderId,build.buildName,r.roomAddress,r.unit,r.floor,r.room,r.direction,r.roomType,r.propertyRight,r.insideArea,r.balconyArea,r.proportionArea,r.completeDate,r.propertyRightId,r.soilId,r.ispublished,r.isvacantRoom,r.userId,r.latestDate,d1.dictItemName,d2.dictItemName,d3.dictItemName,build.floorNum)" +
		    " from scsc.pub.data.RoomMng as r,scsc.pub.data.HouseBuilding as build,scsc.pub.data.SysDictItem as d1,scsc.pub.data.SysDictItem as d2,scsc.pub.data.SysDictItem as d3 " +
		    " where r.builderId=build.buildId and r.direction=d1.dictItemId and d1.dictId='000210' and r.roomType=d2.dictItemId and d2.dictId='000204' and r.propertyRight=d3.dictItemId and d3.dictId='000211'";
		    if (formdata.getBuildId()!=null && formdata.getBuildId().intValue()!=0)
		    	sqlStr=sqlStr + " and r.builderId=" + formdata.getBuildId() ;
		    if (formdata.getUnit()!=null && formdata.getUnit().intValue()!=0)
		    	sqlStr=sqlStr + " and r.unit=" + formdata.getUnit() ;
		    if (formdata.getFloor()!=null && formdata.getFloor().intValue()!=0)
		    	sqlStr=sqlStr + " and r.floor=" + formdata.getFloor() ;
		    if (formdata.getRoomType()!=null && formdata.getRoomType().length()>0)
		    	sqlStr=sqlStr + " and r.roomType like '%" + formdata.getRoomType() + "%'";
		    if (formdata.getPropertyRight()!=null && formdata.getPropertyRight() .length()>0)
		    	sqlStr=sqlStr + " and r.propertyRight like '%" + formdata.getPropertyRight() + "%'";
		    if (formdata.getIsvacant ()!=null && formdata.getIsvacant ().length()>0)
		    	sqlStr=sqlStr + " and r.isvacantRoom like '%" + formdata.getIsvacant () + "%'";
		    sqlStr+=" order by r.builderId,r.unit,r.floor,r.room";
	  }else{
		  sqlStr="select new scsc.web.xzgl.building.report.data.RoomDetailRptData(";
		  sqlStr+="h.holderName,dept.deptname,hb.buildName,r.unit,r.floor,r.room,r.roomType,d1.dictItemName,r.propertyRight,d2.dictItemName,r.propertyRightId,h.enterTime";
		  sqlStr+=")";
		  sqlStr+=" from scsc.pub.data.RoomMng as r,scsc.pub.data.HouseAlteration as h,scsc.pub.data.HouseBuilding as hb,scsc.pub.data.SysDictItem as d1,scsc.pub.data.SysDictItem as d2,scsc.pub.data.Department as dept";
	//	    if (formdata.getRoomType()!=null && formdata.getRoomType().trim().length()>0 && !formdata.getRoomType().trim().equals("204000")){
	//			sqlStr=sqlStr+",scsc.pub.data.SysDictItem as d1";
	//		}
	//		if (formdata.getPropertyRight()!=null && formdata.getPropertyRight().trim().length()>0 && !formdata.getPropertyRight().trim().equals("211000")){
	//			sqlStr=sqlStr+",scsc.pub.data.SysDictItem as d2";
	//		}
		  sqlStr+=" where r.roomId=h.roomId and r.builderId=hb.buildId and h.isnowHolder='1'";
		  sqlStr+=" and r.roomType=d1.dictItemId and d1.dictId='000204' ";
		  sqlStr+=" and r.propertyRight=d2.dictItemId and d2.dictId='000211'";
		  sqlStr+=" and h.holderCorpName=dept.deptcode";
		  if (formdata.getHolderCorpName()!=null && formdata.getHolderCorpName().trim().length()>0){
				sqlStr=sqlStr+ " and h.holderCorpName like '%" + formdata.getHolderCorpName().trim() + "%'";
			}
		  if (formdata.getBuildId()!=null && formdata.getBuildId().intValue()!=0){
				sqlStr=sqlStr+" and hb.buildId=" + formdata.getBuildId();
			}
			if (formdata.getUnit()!=null && formdata.getUnit().intValue()!=0){
				sqlStr=sqlStr+" and r.unit=" + formdata.getUnit();
			}
			if (formdata.getFloor()!=null && formdata.getFloor().intValue()!=0){
				sqlStr=sqlStr+" and r.floor=" + formdata.getFloor();
			}
			if (formdata.getRoomType()!=null && formdata.getRoomType().trim().length()>0 && !formdata.getRoomType().trim().equals("204000")){
				sqlStr=sqlStr+" and r.roomType like '%" +formdata.getRoomType().trim()+ "%'";
			}
			if (formdata.getPropertyRight()!=null && formdata.getPropertyRight().trim().length()>0 && !formdata.getPropertyRight().trim().equals("211000")){
				sqlStr=sqlStr+" and r.propertyRight like '%" + formdata.getPropertyRight().trim()+ "%'";
			}
			if (formdata.getIsvacant()!=null && formdata.getIsvacant().trim().length()>0){
				sqlStr=sqlStr+" and r.isvacantRoom like '%"+ formdata.getIsvacant().trim()+ "%'";
			}
			sqlStr=sqlStr + " order by dept.deptname,r.unit,r.floor,r.room";
	  	}
		dbmanage.sqlQueryPage(sqlStr, footer);
  }
  
  public ArrayList selectCrossChart(DBManage dbmanage){
	  String sqlStr="select hb.buildId,hb.buildName,r.roomType,d.dictItemName,ha.holderCorpName,dept.deptname,count(*) as count" +
	  		" from scsc.pub.data.HouseAlteration as ha,scsc.pub.data.RoomMng as r," +
	  		"scsc.pub.data.HouseBuilding as hb,scsc.pub.data.SysDictItem as d,scsc.pub.data.Department as dept " +
	  		"where ha.roomId=r.roomId and r.builderId=hb.buildId and r.roomType=d.dictItemId and d.dictId='000204'" +
	  		"and ha.holderCorpName=dept.deptcode " +
	  		"group by hb.buildId,hb.buildName,r.roomType,d.dictItemName,ha.holderCorpName,dept.deptname" +
	  		" order by hb.buildId";
	  List result = dbmanage.sqlQuery(sqlStr);
	  return (ArrayList) result;
	  
  }
}
